package com.dayuanit.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;

import com.dayuanit.dao.UserDao;
import com.dayuanit.domain.User;


public class UserDaoImpl implements UserDao{

	public int add(User user, Connection conn) {
		String sql = "insert into atm_user(username, card_num, balance, create_time) values(?, ?, ?, now());";
		System.out.println(sql);

		PreparedStatement psm = null;
		int id = 0;
		ResultSet resultSet = null;

		try {
			psm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			psm.setString(1, user.username);
			psm.setString(2, user.cardNum);
			psm.setInt(3, user.balance);
			
			psm.executeUpdate();
			
			resultSet = psm.getGeneratedKeys();
			while (resultSet.next()) {
				id = resultSet.getInt(1);
				System.out.println("userDao insert id=" + id);
			}
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != resultSet){
					resultSet.close();
				}
				if (null != psm) {
					psm.close();
				}				
			} catch(SQLException se) {
				se.printStackTrace();
			}
			
		}

		return id;
	}

	public int modifyBalance(int newBalance, int oldBalance, int userId, Connection conn) {
		String sql = "update atm_user set balance = ? where id = ? and balance = ?;";
		System.out.println(sql);

		int rows = 0;
		PreparedStatement psm = null;

		try {
			psm = conn.prepareStatement(sql);
			psm.setInt(1, newBalance);
			psm.setInt(2, userId);
			psm.setInt(3, oldBalance);
			
			rows = psm.executeUpdate();
			
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != psm) {
					psm.close();
				}
			} catch(SQLException se) {
				se.printStackTrace();
			}
			
		}

		return rows;
	}


	public User queryUser(String cardNum, Connection conn) {
		String sql = "select id, username, card_num, balance, create_time from atm_user where card_num = ?;";
		
		PreparedStatement psm = null;
		ResultSet rs = null;

		User user = null;

		try {
			
			psm = conn.prepareStatement(sql);
			psm.setString(1, cardNum);
			
			System.out.println(sql);
			
			rs = psm.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
				int balance = rs.getInt("balance");
				Timestamp tt = rs.getTimestamp("create_time");

				user = new User();
				user.id = id;
				user.username = username;
				user.cardNum = cardNum;
				user.balance = balance;
				user.createTime = new Date(tt.getTime());
				break;
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != psm) {
					psm.close();
				}
			} catch(SQLException se) {
				se.printStackTrace();
			}
			
		}

		return user;
	}

}